1 Imports System.Data.SqlClient
2 Public Class frmBusCardHolder_Staff
3 Private Sub auto()
4 Try
5 Dim Num As Integer = 0
6 con = New SqlConnection(cs)
7 con.Open()
8 Dim sql As String = ("SELECT MAX(BCH_ID) FROM BusCardHolder_Staff")
9 cmd = New SqlCommand(sql)
10 cmd.Connection = con
11 If (IsDBNull(cmd.ExecuteScalar)) Then
12 Num = 1
13 txtID.Text = Num.ToString
14 Else
15 Num = cmd.ExecuteScalar + 1
16 txtID.Text = Num.ToString
17 End If
18 cmd.Dispose()
19 con.Close()
20 con.Dispose()
21 Catch ex As Exception
22 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
23 End Try
24 End Sub
25 Sub fillLocationName()
26 Try
27 Dim CN As New SqlConnection(cs)
28 CN.Open()
29 adp = New SqlDataAdapter()
30 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(LocationName) FROM Location", CN)
31 ds = New DataSet("ds")
32 adp.Fill(ds)
33 dtable = ds.Tables(0)
34 cmbLocationName.Items.Clear()
35 For Each drow As DataRow In dtable.Rows
36 cmbLocationName.Items.Add(drow(0).ToString())
37 Next
38
39 Catch ex As Exception
40 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
41 End Try
42 End Sub
43 Sub fillBusNo()
44 Try
45 Dim CN As New SqlConnection(cs)
46 CN.Open()
47 adp = New SqlDataAdapter()
48 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(BusNo) FROM BusInfo", CN)
49 ds = New DataSet("ds")
50 adp.Fill(ds)
51 dtable = ds.Tables(0)
52 cmbBusNo.Items.Clear()
53 For Each drow As DataRow In dtable.Rows
54 cmbBusNo.Items.Add(drow(0).ToString())
55 Next
56
57 Catch ex As Exception
58 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
59 End Try
60 End Sub
61 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
62 Me.Close()
63 End Sub
64 Sub Reset()
65 dtpJoiningDate.Text = Today
66 cmbStatus.SelectedIndex = -1
67 cmbBusNo.SelectedIndex = -1
68 txtStaffName.Text = ""
69 txtS_ID.Text = ""
70 txtStaffID.Text = ""
71 txtSchoolName.Text = ""
72 cmbLocationName.SelectedIndex = -1
73 btnSave.Enabled = True
74 btnUpdate.Enabled = False
75 btnDelete.Enabled = False
76 txtStaffID.Focus()
77 auto()
78 End Sub
79 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
80 Reset()
81 End Sub
82
83 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
84 If Len(Trim(txtStaffID.Text)) = 0 Then
85 MessageBox.Show("Please retrieve staff id", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
86 txtStaffID.Focus()
87 Exit Sub
88 End If
89 If Len(Trim(cmbBusNo.Text)) = 0 Then
90 MessageBox.Show("Please select bus no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
91 cmbBusNo.Focus()
92 Exit Sub
93 End If
94 If Len(Trim(cmbLocationName.Text)) = 0 Then
95 MessageBox.Show("Please select Location name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
96 cmbLocationName.Focus()
97 Exit Sub
98 End If
99 If Len(Trim(cmbStatus.Text)) = 0 Then
100 MessageBox.Show("Please select status", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
101 cmbStatus.Focus()
102 Exit Sub
103 End If
104 Try
105 con = New SqlConnection(cs)
106 con.Open()
107 Dim ct As String = "select StaffID from BusCardHolder_Staff where StaffID=@d1"
108 cmd = New SqlCommand(ct)
109 cmd.Connection = con
110 cmd.Parameters.AddWithValue("@d1", txtS_ID.Text)
111 rdr = cmd.ExecuteReader()
112 If rdr.Read Then
113 MessageBox.Show("Record already exists", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
114 Reset()
115 If Not rdr Is Nothing Then
116 rdr.Close()
117 End If
118 Exit Sub
119 End If
120 con = New SqlConnection(cs)
121 con.Open()
122 Dim cb As String = "insert into BusCardHolder_Staff(BCH_ID,StaffID, Location, JoiningDate, Status,BusNo) VALUES (" & txtID.Text & ",@d1,@d2,@d3,@d4,@d5)"
123 cmd = New SqlCommand(cb)
124 cmd.Connection = con
125 cmd.Parameters.AddWithValue("@d1", txtS_ID.Text)
126 cmd.Parameters.AddWithValue("@d2", cmbLocationName.Text)
127 cmd.Parameters.AddWithValue("@d3", CDate(dtpJoiningDate.Text))
128 cmd.Parameters.AddWithValue("@d4", cmbStatus.Text)
129 cmd.Parameters.AddWithValue("@d5", cmbBusNo.Text)
130 cmd.ExecuteNonQuery()
131 LogFunc(lblUser.Text, "added new bus holder '" & txtStaffName.Text & "' having staff id '" & txtStaffID.Text & "'")
132 MessageBox.Show("Successfully saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
133 btnSave.Enabled = False
134 con.Close()
135 Catch ex As Exception
136 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
137 End Try
138 End Sub
139
140 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
141 If Len(Trim(txtStaffID.Text)) = 0 Then
142 MessageBox.Show("Please retrieve staff id", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
143 txtStaffID.Focus()
144 Exit Sub
145 End If
146 If Len(Trim(cmbBusNo.Text)) = 0 Then
147 MessageBox.Show("Please select bus no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
148 cmbBusNo.Focus()
149 Exit Sub
150 End If
151 If Len(Trim(cmbLocationName.Text)) = 0 Then
152 MessageBox.Show("Please select Location name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
153 cmbLocationName.Focus()
154 Exit Sub
155 End If
156 If Len(Trim(cmbStatus.Text)) = 0 Then
157 MessageBox.Show("Please select status", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
158 cmbStatus.Focus()
159 Exit Sub
160 End If
161 Try
162 con = New SqlConnection(cs)
163 con.Open()
164 Dim cb As String = "Update BusCardHolder_Staff set StaffID=@d1, Location=@d2, JoiningDate=@d3, Status=@d4,BusNo=@d5 where BCH_ID=" & txtID.Text & ""
165 cmd = New SqlCommand(cb)
166 cmd.Connection = con
167 cmd.Parameters.AddWithValue("@d1", txtS_ID.Text)
168 cmd.Parameters.AddWithValue("@d2", cmbLocationName.Text)
169 cmd.Parameters.AddWithValue("@d3", CDate(dtpJoiningDate.Text))
170 cmd.Parameters.AddWithValue("@d4", cmbStatus.Text)
171 cmd.Parameters.AddWithValue("@d5", cmbBusNo.Text)
172 cmd.ExecuteNonQuery()
173 LogFunc(lblUser.Text, "updated the record of bus holder '" & txtStaffName.Text & "' having staff id '" & txtStaffID.Text & "'")
174 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
175 btnUpdate.Enabled = False
176 con.Close()
177 Catch ex As Exception
178 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
179 End Try
180 End Sub
181
182 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
183 Try
184 If MessageBox.Show("Do you really want to delete the record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = Windows.Forms.DialogResult.Yes Then
185 delete_records()
186 End If
187 Catch ex As Exception
188 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
189 End Try
190 End Sub
191 Private Sub delete_records()
192 Try
193 Dim RowsAffected As Integer = 0
194 con = New SqlConnection(cs)
195 con.Open()
196 Dim cl As String = "select BusHolderID from BusCardHolder_Staff,BusFeePayment_Staff where BusCardHolder_Staff.BCH_ID=BusFeePayment_Staff.BusHolderID and BusHolderID=@d1"
197 cmd = New SqlCommand(cl)
198 cmd.Connection = con
199 cmd.Parameters.AddWithValue("@d1", txtID.Text)
200 rdr = cmd.ExecuteReader()
201 If rdr.Read Then
202 MessageBox.Show("Unable to delete..Already in use in Bus Fee Payment [Staff]", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
203 If Not rdr Is Nothing Then
204 rdr.Close()
205 End If
206 Exit Sub
207 End If
208 con = New SqlConnection(cs)
209 con.Open()
210 Dim cq As String = "delete from BusCardHolder_Staff where BCH_ID= " & txtID.Text & ""
211 cmd = New SqlCommand(cq)
212 cmd.Connection = con
213 RowsAffected = cmd.ExecuteNonQuery()
214 If RowsAffected > 0 Then
215 LogFunc(lblUser.Text, "deleted the bus holder '" & txtStaffName.Text & "' having staff id '" & txtStaffID.Text & "'")
216 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
217 Reset()
218 Else
219 MessageBox.Show("No record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
220 Reset()
221 If con.State = ConnectionState.Open Then
222
223 con.Close()
224 End If
225
226 con.Close()
227 End If
228 Catch ex As Exception
229 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
230 End Try
231 End Sub
232
233 Private Sub frmBusCardHolder_Student_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
234 fillLocationName()
235 fillBusNo()
236 End Sub
237
238 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
239 frmStaffRecord.Reset()
240 frmStaffRecord.lblSet.Text = "Bus Holder Entry"
241 frmStaffRecord.ShowDialog()
242 End Sub
243
244 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
245 frmBusCardHolder_StaffRecord.Reset()
246 frmBusCardHolder_StaffRecord.lblSet.Text = "Bus Holder Entry"
247 frmBusCardHolder_StaffRecord.ShowDialog()
248 End Sub
249 End Class
